﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace planetExpressLib
{
    public class reservaCAD
    {
        string cadena;

        private List<reservaEN> allReservas;

        public reservaCAD()
        {
            allReservas = new List<reservaEN>();
            cadena = ConfigurationManager.ConnectionStrings["DatabaseConnection"].ToString();
        }

        public void insert(reservaEN reserva)
        {
            SqlConnection c = new SqlConnection(cadena);

            try
            {
                c.Open();
                string sentencia = "INSERT INTO RESERVA(Cliente, Destino, Oficina, Fecha, PrecioTotal, Tipo) VALUES(";
                sentencia += reserva.Cliente + ",";
                sentencia += reserva.Destino + ", ";
                sentencia += reserva.Oficina + ", ";
                sentencia += reserva.Fecha + ", ";
                sentencia += reserva.PrecioTotal + ", ";
                sentencia += reserva.Tipo + ")";

                SqlCommand com = new SqlCommand(sentencia,c);

                com.ExecuteNonQuery();
            }
            catch(Exception e)
            {
                throw e;
            }
            finally
            {
                c.Close();
            }


        }

        public void update(reservaEN reserva)
        {
            SqlConnection c = new SqlConnection(cadena);

            try
            {
                c.Open();

                string sentencia = "UPDATE RESERVA SET ";
                sentencia += "Cliente = " + reserva.Cliente;
                sentencia += ", Destino = " + reserva.Destino;
                sentencia += ", Oficina = " + reserva.Oficina;
                sentencia += ", Fecha = " + reserva.Fecha;
                sentencia += ", PrecioTotal =" + reserva.PrecioTotal;
                sentencia += ", Tipo = " + reserva.Tipo;
                sentencia += "WHERE Id = " + reserva.IdReserva;

                SqlCommand com = new SqlCommand(sentencia,c);

                com.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                c.Close();
            }

        }

        public void delete(int id)
        {
            SqlConnection c = new SqlConnection(cadena);

            try
            {
                c.Open();

                string sentencia = "DELETE FROM RESERVA WHERE Id = " + id;

                SqlCommand com = new SqlCommand(sentencia, c);

                com.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                c.Close();
            }
        }

        public reservaEN readID(int id)
        {
            reservaEN reserva = null;

            SqlConnection c = new SqlConnection(cadena);
            SqlDataReader dr = null;

            try
            {
                c.Open();

                string sentencia = "SELECT * FROM RESERVA WHERE Id = " + id;

                SqlCommand com = new SqlCommand(sentencia, c);

                dr = com.ExecuteReader();

                if (dr.Read())
                {
                    reserva.IdReserva = (int)dr["Id"];
                    reserva.Cliente = new clientesCAD().readID((int)dr["Cliente"]);
                    reserva.Destino = new planetaCAD().readID((int)dr["Destino"]);
                    reserva.Oficina = new oficinaCAD().readID((int)dr["Oficina"]);
                    reserva.Fecha = (DateTime)dr["Fecha"];
                    reserva.PrecioTotal = (float)dr["PrecioTotal"];
                    reserva.Tipo = new tipoViajeCAD().readID((int)dr["Tipo"]);
                }

            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                dr.Close();
                c.Close();
            }

            return reserva;
        }

        public List<reservaEN> readAll()
        {
            SqlConnection c = new SqlConnection(cadena);
            SqlDataReader dr = null;
            reservaEN nueva = null;

            try
            {
                c.Open();

                string sentencia = "SELECT * FROM RESERVA";

                SqlCommand com = new SqlCommand(sentencia, c);

                dr = com.ExecuteReader();

                while (dr.Read())
                {
                    nueva.IdReserva = (int)dr["Id"];

                    nueva.Cliente = new clientesCAD().readID((int)dr["Cliente"]);
                    nueva.Destino = new planetaCAD().readID((int)dr["Destino"]);
                    nueva.Oficina = new oficinaCAD().readID((int)dr["Oficina"]);
                    nueva.Fecha = (DateTime)dr["Fecha"];
                    nueva.PrecioTotal = (float)dr["PrecioTotal"];
                    nueva.Tipo = new tipoViajeCAD().readID((int)dr["Tipo"]);

                    allReservas.Add(nueva);
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                dr.Close();
                c.Close();
            }

            return allReservas;
        }

    }
}